﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Expo.Training.CustomerData;
using System.Data.SqlClient;
using System.Data;

namespace Expo.Training.DataAccess.Activity
{
    public class ActivityModule
    {

        #region 根据id获取activity
        public static ActivityEntity GetActivity(Guid id)
        {
            ActivityEntity _model = new ActivityEntity();
            _model.ActivityID = id;

            Database objDB = new Database();
            SqlDataReader objReader = null;
            try
            {
                SqlParameter[] prams = {
                    objDB.MakeInParam("@activityID", System.Data.SqlDbType.UniqueIdentifier, 32, id)
                };
                objDB.RunProc("sp_Activity_Info_XB_GetModelByID", prams, out objReader);
                while (objReader.Read())
                {
                    _model.ActivityID = new Guid(objReader["ActivityID"].ToString().Trim());
                    _model.ActivityTitle = objReader["ActivityTitle"].ToString();
                    _model.ActivityIntro = objReader["ActivityIntro"].ToString();
                    _model.Count = int.Parse(objReader["Count"].ToString());
                    _model.Url = objReader["Url"].ToString();
                    _model.Destination = objReader["Destination"].ToString();

                    if (string.IsNullOrEmpty(objReader["BeginDate"].ToString()))
                    {
                        _model.BeginDate = null;
                    }
                    else
                    {
                        _model.BeginDate = DateTime.Parse(objReader["BeginDate"].ToString());
                    }

                    if (string.IsNullOrEmpty(objReader["EndDate"].ToString()))
                    {
                        _model.EndDate = null;
                    }
                    else
                    {
                        _model.EndDate = DateTime.Parse(objReader["EndDate"].ToString());
                    }

                    if (string.IsNullOrEmpty(objReader["AddTime"].ToString()))
                    {
                        _model.AddTime = null;
                    }
                    else
                    {
                        _model.AddTime = DateTime.Parse(objReader["AddTime"].ToString());
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return _model;
        }
        #endregion

        #region 获取全部activity
        public static List<ActivityEntity> GetAll()
        {
            List<ActivityEntity> _list = new List<ActivityEntity>();

            Database objDB = new Database();
            SqlDataReader objReader = null;
            
            try
            {
                SqlParameter[] prams = {};
                objDB.RunProc("sp_Activity_Info_XB_GetAll", prams, out objReader);
                while (objReader.Read())
                {
                    ActivityEntity _model = new ActivityEntity();
                    _model.ActivityID = new Guid(objReader["ActivityID"].ToString().Trim());
                    _model.ActivityTitle = objReader["ActivityTitle"].ToString();
                    _model.ActivityIntro = objReader["ActivityIntro"].ToString();
                    _model.Count = int.Parse(objReader["Count"].ToString());
                    _model.Url = objReader["Url"].ToString();
                    _model.Destination = objReader["Destination"].ToString();

                    if(string.IsNullOrEmpty(objReader["BeginDate"].ToString())){
                        _model.BeginDate = null;
                    }else{
                        _model.BeginDate = DateTime.Parse(objReader["BeginDate"].ToString());
                    }

                    if (string.IsNullOrEmpty(objReader["EndDate"].ToString()))
                    {
                        _model.EndDate = null;
                    }
                    else
                    {
                        _model.EndDate = DateTime.Parse(objReader["EndDate"].ToString());
                    }

                    if (string.IsNullOrEmpty(objReader["AddTime"].ToString()))
                    {
                        _model.AddTime = null;
                    }
                    else
                    {
                        _model.AddTime = DateTime.Parse(objReader["AddTime"].ToString());
                    }

                    _list.Add(_model);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return _list;
        }
        #endregion

        #region 添加activity
        public static bool Add(ActivityEntity model)
        {
            Database objDB = new Database();
            bool isSuccess = false;

            try
            {
                SqlParameter[] prams = {
                    
                    objDB.MakeInParam("@ActivityTitle", System.Data.SqlDbType.NVarChar, 200, model.ActivityTitle),
                    objDB.MakeInParam("@ActivityIntro", System.Data.SqlDbType.NText, 2000, model.ActivityIntro),
                    objDB.MakeInParam("@Count", System.Data.SqlDbType.Int, 4, model.Count),
                    objDB.MakeInParam("@BeginDate", System.Data.SqlDbType.SmallDateTime, 4, model.BeginDate),
                    objDB.MakeInParam("@EndDate", System.Data.SqlDbType.SmallDateTime, 4, model.EndDate),
                    objDB.MakeInParam("@Url", System.Data.SqlDbType.VarChar, 100, model.Url),
                    objDB.MakeInParam("@Destination", System.Data.SqlDbType.VarChar, 100, model.Destination),
                    objDB.MakeInParam("@AddTime", System.Data.SqlDbType.SmallDateTime, 4, model.AddTime)

                };
                objDB.RunProc("sp_Activity_Info_XB_Add", prams);

                isSuccess = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return isSuccess;
        }
        #endregion

        #region 更新activity
        public static bool Update(ActivityEntity model)
        {
            Database objDB = new Database();
            bool isSuccess = false;

            try
            {
                SqlParameter[] prams = {
                    
                    objDB.MakeInParam("@ActivityTitle", System.Data.SqlDbType.NVarChar, 200, model.ActivityTitle),
                    objDB.MakeInParam("@ActivityIntro", System.Data.SqlDbType.NText, 2000, model.ActivityIntro),
                    objDB.MakeInParam("@Count", System.Data.SqlDbType.Int, 4, model.Count),
                    objDB.MakeInParam("@BeginDate", System.Data.SqlDbType.SmallDateTime, 4, model.BeginDate),
                    objDB.MakeInParam("@EndDate", System.Data.SqlDbType.SmallDateTime, 4, model.EndDate),
                    objDB.MakeInParam("@Url", System.Data.SqlDbType.VarChar, 100, model.Url),
                    objDB.MakeInParam("@Destination", System.Data.SqlDbType.VarChar, 100, model.Destination),
                    objDB.MakeInParam("@AddTime", System.Data.SqlDbType.SmallDateTime, 4, model.AddTime),
                    objDB.MakeInParam("@CustomCondition", System.Data.SqlDbType.NVarChar, 500, "ActivityID ='" + model.ActivityID.ToString() + "'")

                };
                objDB.RunProc("sp_Activity_Info_XB_Update", prams);

                isSuccess = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return isSuccess;
        }
        #endregion

        #region 删除activity
        public static bool Delete(Guid id)
        {
            Database objDB = new Database();
            bool isSuccess = false;

            try
            {
                SqlParameter[] prams = {
                    objDB.MakeInParam("@ActivityID", System.Data.SqlDbType.UniqueIdentifier, 32, id)
                };
                objDB.RunProc("sp_Activity_Info_XB_Delete", prams);

                isSuccess = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return isSuccess;
        }
        #endregion

        #region 根据activityId获取活动关联课程
        public static List<ActivityCourseDetailsEntity> GetCoursesDetailsByActivityID(Guid id)
        {
            List<ActivityCourseDetailsEntity> _list = new List<ActivityCourseDetailsEntity>();

            Database objDB = new Database();
            SqlDataReader objReader = null;

            try
            {
                SqlParameter[] prams = {
                    objDB.MakeInParam("@ActivityID", System.Data.SqlDbType.UniqueIdentifier, 32, id)
                };

                objDB.RunProc("sp_Activity_Course_XB_GetDetailsByActivityID", prams, out objReader);
                while (objReader.Read())
                {
                    ActivityCourseDetailsEntity _model = new ActivityCourseDetailsEntity();
                    _model.ActivityID = id;
                    _model.CourseID = new Guid(objReader["CourseID"].ToString().Trim());
                    _model.AddTime = DateTime.Parse(objReader["AddTime"].ToString());
                    _model.CourseName = objReader["CourseName"].ToString();
                    _model.CourseAddTime = DateTime.Parse(objReader["AddTime"].ToString());

                    _list.Add(_model);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return _list;
        }
        #endregion

        #region 根据courseId获取活动信息
        public static List<ActivityEntity> GetActivitysByCourseID(Guid id)
        {
            List<ActivityEntity> _list = new List<ActivityEntity>();

            Database objDB = new Database();
            SqlDataReader objReader = null;

            try
            {
                SqlParameter[] prams = {
                    objDB.MakeInParam("@CourseID", System.Data.SqlDbType.UniqueIdentifier, 32, id)
                };

                objDB.RunProc("sp_Activity_Course_XB_GetActivitysByCourseID", prams, out objReader);
                while (objReader.Read())
                {
                    ActivityEntity _model = new ActivityEntity();

                    _model.ActivityID = new Guid(objReader["ActivityID"].ToString().Trim());
                    _model.ActivityTitle = objReader["ActivityTitle"].ToString();
                    _model.ActivityIntro = objReader["ActivityIntro"].ToString();
                    _model.Count = int.Parse(objReader["Count"].ToString());
                    _model.Url = objReader["Url"].ToString();
                    _model.Destination = objReader["Destination"].ToString();

                    if (string.IsNullOrEmpty(objReader["BeginDate"].ToString()))
                    {
                        _model.BeginDate = null;
                    }
                    else
                    {
                        _model.BeginDate = DateTime.Parse(objReader["BeginDate"].ToString());
                    }

                    if (string.IsNullOrEmpty(objReader["EndDate"].ToString()))
                    {
                        _model.EndDate = null;
                    }
                    else
                    {
                        _model.EndDate = DateTime.Parse(objReader["EndDate"].ToString());
                    }

                    if (string.IsNullOrEmpty(objReader["AddTime"].ToString()))
                    {
                        _model.AddTime = null;
                    }
                    else
                    {
                        _model.AddTime = DateTime.Parse(objReader["AddTime"].ToString());
                    }

                    _list.Add(_model);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return _list;
        }

        #endregion


        #region 检查课程是否已被关联到指定活动
        public static bool IsExistOfCourse(Guid activityId, Guid courseId)
        {
            bool _isExist = false;

            List<ActivityCourseDetailsEntity> _list = new List<ActivityCourseDetailsEntity>();
            _list = GetCoursesDetailsByActivityID(activityId);

            if (_list.Count > 0)
            {
                var _items = from course in _list
                              where course.CourseID == courseId
                              select course;

                if (_items.Count() > 0)
                {
                    _isExist = true;
                }
            }
            return _isExist;
        }
        #endregion

        #region 添加关联
        public static bool AddActivityCourse(Guid activityId, Guid courseId)
        {
            Database objDB = new Database();
            bool _isSuccess = false;

            try
            {
                SqlParameter[] prams = {
                    
                    objDB.MakeInParam("@ActivityID", System.Data.SqlDbType.UniqueIdentifier, 32, activityId),
                    objDB.MakeInParam("@CourseID", System.Data.SqlDbType.UniqueIdentifier, 32, courseId)

                };
                objDB.RunProc("sp_Activity_Course_XB_Add", prams);

                _isSuccess = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return _isSuccess;
        }
        #endregion

        #region 删除关联
        public static bool DeleteActivityCourse(Guid activityId, Guid courseId)
        {
            Database objDB = new Database();
            bool _isSuccess = false;

            try
            {
                SqlParameter[] prams = {
                    
                    objDB.MakeInParam("@ActivityID", System.Data.SqlDbType.UniqueIdentifier, 32, activityId),
                    objDB.MakeInParam("@CourseID", System.Data.SqlDbType.UniqueIdentifier, 32, courseId)

                };
                objDB.RunProc("sp_Activity_Course_XB_Delete", prams);

                _isSuccess = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return _isSuccess;
        }
        #endregion

        #region 添加报名
        public static bool AddRegistration(ActivityRegistrationEntity model)
        {
            Database objDB = new Database();
            bool _isSuccess = false;

            try
            {
                SqlParameter[] prams = {
                    
                    objDB.MakeInParam("@ActivityID", System.Data.SqlDbType.UniqueIdentifier, 32, model.ActivityID),
                    objDB.MakeInParam("@MemberID", System.Data.SqlDbType.UniqueIdentifier, 32, model.MemberID),
                    objDB.MakeInParam("@AddTime", System.Data.SqlDbType.SmallDateTime, 4, model.AddTime),
                    objDB.MakeInParam("@RealName", System.Data.SqlDbType.NVarChar, 50, model.RealName),
                    objDB.MakeInParam("@Sex", System.Data.SqlDbType.Int, 4, model.Sex),
                    objDB.MakeInParam("@Age", System.Data.SqlDbType.Int, 4, model.Age),
                    objDB.MakeInParam("@FamilyAddress", System.Data.SqlDbType.NVarChar, 200, model.FamilyAddress),
                    objDB.MakeInParam("@ContactAddress", System.Data.SqlDbType.NVarChar, 200, model.ContactAddress),
                    objDB.MakeInParam("@Mobile", System.Data.SqlDbType.NVarChar, 50, model.Mobile),
                    objDB.MakeInParam("@BeginDate", System.Data.SqlDbType.SmallDateTime, 4, model.BeginDate),
                    objDB.MakeInParam("@EndDate", System.Data.SqlDbType.SmallDateTime, 4, model.EndDate),
                    objDB.MakeInParam("@Intention", System.Data.SqlDbType.NVarChar, 50, model.Intention),
                    objDB.MakeInParam("@Requirement", System.Data.SqlDbType.NVarChar, 500, model.Requirement),
                    objDB.MakeInParam("@RegNumber", System.Data.SqlDbType.Int, 4, model.RegNumber),
                    objDB.MakeInParam("@Comments", System.Data.SqlDbType.NVarChar, 500, model.Comments),
                    objDB.MakeInParam("@Status", System.Data.SqlDbType.Int, 4, model.Status)

                };
                objDB.RunProc("sp_Activity_Registration_XB_Add", prams);

                _isSuccess = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return _isSuccess;
        }
        #endregion

        #region 删除报名
        public static bool DeleteRegistration(Guid id)
        {
            Database objDB = new Database();
            bool _isSuccess = false;

            try
            {
                SqlParameter[] prams = {
                    
                    objDB.MakeInParam("@RegistrationID", System.Data.SqlDbType.UniqueIdentifier, 32, id)
                    
                };
                objDB.RunProc("sp_Activity_Registration_XB_Delete", prams);

                _isSuccess = true;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return _isSuccess;
        }
        #endregion

        #region 根据条件获取报名信息的泛型列表
        public static List<ActivityRegistrationDetailsEntity> GetRegistrations(string condition)
        {
            List<ActivityRegistrationDetailsEntity> _list = new List<ActivityRegistrationDetailsEntity>();

            Database objDB = new Database();
            SqlDataReader objReader = null;

            try
            {
                if (string.IsNullOrEmpty(condition))
                {
                    SqlParameter[] prams = {};
                    objDB.RunProc("sp_Activity_Registration_XB_Query", prams, out objReader);
                }
                else
                {
                    SqlParameter[] prams = {
                        objDB.MakeInParam("@CustomCondition", System.Data.SqlDbType.VarChar, 200, condition)
                    };
                    objDB.RunProc("sp_Activity_Registration_XB_Query", prams, out objReader);
                }

                
                while (objReader.Read())
                {
                    ActivityRegistrationDetailsEntity _model = new ActivityRegistrationDetailsEntity();

                    _model.RegistrationID = new Guid(objReader["RegistrationID"].ToString().Trim());
                    _model.ActivityID = new Guid(objReader["ActivityID"].ToString().Trim());
                    _model.MemberID = new Guid(objReader["MemberID"].ToString().Trim());
                    _model.AddTime = DateTime.Parse(objReader["AddTime"].ToString());
                    _model.RealName = objReader["RealName"].ToString();
                    _model.Sex = int.Parse(objReader["Sex"].ToString());
                    _model.Age = int.Parse(objReader["Age"].ToString());
                    _model.FamilyAddress = objReader["FamilyAddress"].ToString();
                    _model.ContactAddress = objReader["ContactAddress"].ToString();
                    _model.Mobile = objReader["Mobile"].ToString();
                    _model.BeginDate = DateTime.Parse(objReader["BeginDate"].ToString());
                    _model.EndDate = DateTime.Parse(objReader["EndDate"].ToString());
                    _model.Intention = objReader["Intention"].ToString();
                    _model.Requirement = objReader["Requirement"].ToString();
                    _model.RegNumber = int.Parse(objReader["RegNumber"].ToString());
                    _model.Comments = objReader["Comments"].ToString();
                    _model.Status = int.Parse(objReader["Status"].ToString());
                    _model.ActivityTitle = objReader["ActivityTitle"].ToString();

                    _list.Add(_model);
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return _list;
        }
        #endregion

        #region 根据条件获取报名信息的数据表

         public static DataTable GetRegistrationsTable(string condition)
        {
            Database objDB = new Database();
            DataTable _dataTable = null;

            try
            {
                if (string.IsNullOrEmpty(condition))
                {
                    SqlParameter[] prams = {};
                     _dataTable = objDB.QueryDataTable("sp_Activity_Registration_XB_Query", prams);
                }
                else
                {
                    SqlParameter[] prams = {
                        objDB.MakeInParam("@CustomCondition", System.Data.SqlDbType.VarChar, 200, condition)
                    };
                     _dataTable = objDB.QueryDataTable("sp_Activity_Registration_XB_Query", prams);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return _dataTable;
        }
        #endregion

        #region 根据条件获取分页报名信息
        public static DataTable GetRegistrationsByPage(int pageSize, int pageIndex, string condition, string orderExpression, out int totalRecord)
        {

            Database objDB = new Database();
            DataSet _dataSet = null;
            DataTable _dataTable = null;

            try
            {
                if (string.IsNullOrEmpty(condition))
                {
                    SqlParameter[] prams = {
                        objDB.MakeInParam("@PageSize", System.Data.SqlDbType.Int, 4, pageSize),
                        objDB.MakeInParam("@PageIndex", System.Data.SqlDbType.Int, 4, pageIndex),
                        objDB.MakeInParam("@OrderExpression", System.Data.SqlDbType.VarChar, 200, orderExpression),
                        objDB.MakeOutParam("@TotalRecord",System.Data.SqlDbType.Int,4)
                    };

                    _dataSet = objDB.ExcuteQuery("sp_Activity_Registration_XB_QueryByPage", prams, out totalRecord);
                    _dataTable = _dataSet.Tables[0];
                }
                else
                {
                    SqlParameter[] prams = {
                        objDB.MakeInParam("@PageSize", System.Data.SqlDbType.Int, 4, pageSize),
                        objDB.MakeInParam("@PageIndex", System.Data.SqlDbType.Int, 4, pageIndex),
                        objDB.MakeInParam("@OrderExpression", System.Data.SqlDbType.VarChar, 200, orderExpression),
                        objDB.MakeOutParam("@TotalRecord",System.Data.SqlDbType.Int,4),
                        objDB.MakeInParam("@CustomCondition", System.Data.SqlDbType.VarChar, 200, condition)
                    };

                    _dataSet = objDB.ExcuteQuery("sp_Activity_Registration_XB_QueryByPage", prams, out totalRecord);
                    _dataTable = _dataSet.Tables[0];
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                objDB.Close();
                objDB.Dispose();
            }

            return _dataTable;
        }
        #endregion

        #region 根据id获取报名信息
        public static ActivityRegistrationDetailsEntity GetRegistration(Guid id)
        {
            try
            {
                List<ActivityRegistrationDetailsEntity> _list = new List<ActivityRegistrationDetailsEntity>();
                string _condition = "2=2";
                _condition += "And RegistrationID='" + id.ToString() + "'";
                _list = GetRegistrations(_condition);

                ActivityRegistrationDetailsEntity _model = new ActivityRegistrationDetailsEntity();
                if (_list.Count > 0)
                {
                    _model = _list[0];
                }

                return _model;
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion
    }
}
